package com.eascs.workflow.sys.dao.impl;

import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.common.util.StringUtil;
import com.eascs.web.sso.User;
import com.eascs.web.sso.UserHolder;
import com.eascs.workflow.auditorReplace.vo.AuditorReVo;
import com.eascs.workflow.common.dao.BaseDao;
import com.eascs.workflow.common.entity.sys.ActOrgEntity;
import com.eascs.workflow.common.entity.sys.HREmployeeAuditor;
import com.eascs.workflow.common.entity.sys.SysRole;
import com.eascs.workflow.common.entity.sys.SysUser;
import com.eascs.workflow.sys.dao.SysOrgDao;
import com.eascs.workflow.sys.dao.SysUserDao;
import com.eascs.workflow.sys.vo.EmployeeVo;
import com.eascs.workflow.sys.vo.UserVo;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;


@Repository
public class SysUserDaoImpl extends BaseDao implements SysUserDao {
	private Logger logger = LoggerFactory.getLogger(this.getClass());
	@Autowired
	private SysOrgDao sysOrgDao;

	@Override
	public List<SysUser> queryByUsernameWithRoles(SysRole role) {
		if (StringUtils.isBlank(role.getId()) || role.getId().equals("null")) {
			logger.info("error,method:SysUserDaoImpl.queryByUsernameWithRoles必要参数不能为空!");
			return null;
		}
		StringBuffer sql = new StringBuffer();
		sql.append(
				" SELECT emp.employee_no,emp.employee_code,emp.employee_namecn,emp.node_id,emp.mobile,emp.operation_dept,emp.creator_no,emp.modifier_no,emp.create_date,emp.modify_date");
		sql.append(" FROM o2o_act_role AS role");
		sql.append(" LEFT JOIN act_org_role_user sur ON sur.role_id = role.id");
		sql.append(" LEFT JOIN o2o_employee emp ON sur.user_no = emp.employee_no");
		sql.append(" WHERE role.id= ? ");
		List<SysUser> list = workflowJdbcSession.findEntitys(sql.toString(), SysUser.class,
				new Object[] { role.getId() });
		return list;
	}

	@Override
	public List<User> getUserByOrgAndRole(String orgNo, String roleNo) {
		String sql = "SELECT e.employee_no as no,e.employee_namecn as cname,e.mobile as mobile"
				+ " FROM o2o_employee e,act_org_role_user ur"
				+ " WHERE e.employee_no = ur.user_no and ur.role_id = ? and e.node_id = ?";
		return workflowJdbcSession.findEntitys(sql, User.class, roleNo, orgNo);
	}

	@Override
	public List<User> getUserByNos(List<String> userNoList) {
		StringBuffer userNoBuf = new StringBuffer();
		for (String userNo : userNoList) {
			userNoBuf.append(userNo).append(",");
		}
		String userNoParam = userNoBuf.substring(0, userNoBuf.length() - 1);
		String sql = "SELECT e.employee_no as no,e.employee_namecn as cname,e.mobile as mobile,"
				+ "e.telephone as phone,e.employee_code as code FROM o2o_employee e WHERE e.is_dimission = 0 and e.employee_no in (";
		sql = sql + userNoParam + " )";
		return workflowJdbcSession.findEntitys(sql, User.class);
	}

	@Override
	public List<User> getUserByNosWithDMS(List<String> userNoList) {
		StringBuffer userNoBuf = new StringBuffer();
		for (String userNo : userNoList) {
			userNoBuf.append(userNo).append(",");
		}
		String userNoParam = userNoBuf.substring(0, userNoBuf.length() - 1);
		String sql = "SELECT e.employee_no as no,e.employee_namecn as cname,e.mobile as mobile,"
				+ "e.telephone as phone,e.employee_code as code FROM o2o_employee e WHERE e.employee_no in (";
		sql = sql + userNoParam + " )";
		return workflowJdbcSession.findEntitys(sql, User.class);
	}

	@Override
	public PageResult<User> searchEmployeePage(Map<String, String> param, int pageSize, int pageIndex) {
		StringBuffer sql = new StringBuffer();
		sql.append(
				"SELECT employee_no no, employee_namecn cname,employee_code code,employee_account account,mobile FROM o2o_employee WHERE 1 = 1");
		List<Object> paramsList = new ArrayList<>();
		if (StringUtils.isNotEmpty(param.get("employeeNamecn"))) {
			sql.append(" and employee_namecn like ? ");
			paramsList.add("%" + param.get("employeeNamecn") + "%");
		}
		if (StringUtils.isNotEmpty(param.get("employeeAccount"))) {
			sql.append(" and employee_account like ? ");
			paramsList.add("%" + param.get("employeeAccount") + "%");
		}
		Object[] params = paramsList.toArray();
		SqlPageQuery<User> sqlPageQuery = new SqlPageQuery<>(User.class, pageSize, pageIndex, sql.toString());
		sqlPageQuery.addParams(params);
		return super.findDataWithPage(sqlPageQuery);
	}

	@Override
	public User getSysUserById(String userNo) {
		String sql = "SELECT employee_no no, employee_namecn cname, employee_code code, employee_account account, mobile , email,case is_dimission when 0 then 'NORMAL' WHEN 1 then 'DMS' else 'LOCK' end status "
				+ " FROM o2o_employee WHERE employee_no = ?";
		return super.findEntity(sql, User.class, userNo);
	}

	@Override
	public List<Object[]> getLikeEmployeeList(String likeStr) {
		String sql = "SELECT employee_no, employee_namecn, CONCAT(employee_code,employee_account) FROM o2o_employee WHERE (employee_namecn LIKE ? OR employee_code LIKE ?) LIMIT 10";
		return super.findObjectsList(sql, "%" + likeStr + "%", "%" + likeStr + "%");
	}

	@Override
	public Boolean checkIsAdmin(String userNo) {
		return this.checkUserRole(userNo, SysRole.ROLE_ADMIN_CODE);
	}

	@Override
	public Boolean checkUserRole(String userNo, String roleCode) {
		String sql = "SELECT COUNT(*) FROM act_org_role_user a,o2o_act_role b WHERE a.role_id=b.id AND  role_code=? AND user_no=? ";
		int count = super.findInteger(sql, roleCode, userNo);
		if (count > 0) {
			return true;
		}
		return false;
	}

	@Override
	public Boolean checkUserRole(String userNo, List<String> roleCodeList) {
		StringBuffer roleCodeBuf = new StringBuffer();
		for (String roleCode : roleCodeList) {
			roleCodeBuf.append("'").append(roleCode).append("'").append(",");
		}
		String roleCodeParam = roleCodeBuf.substring(0, roleCodeBuf.length() - 1);
		String sql = "SELECT COUNT(*) FROM act_org_role_user a,o2o_act_role b WHERE a.role_id=b.id AND user_no=? AND  role_code in (";
		sql = sql + roleCodeParam + ")";
		int count = super.findInteger(sql, userNo);
		if (count > 0) {
			return true;
		}
		return false;
	}

	@Override
	public PageResult<UserVo> getSysUserInSystem(String userNo, String orgId, int pageIndex, int pageSize) {
		SqlPageQuery<UserVo> pageQuery = new SqlPageQuery<UserVo>(UserVo.class, pageSize, pageIndex);
		// StringBuffer sql = new StringBuffer("SELECT e.employee_no
		// userNo,e.employee_namecn userName from act_org_user ou,");
		// sql.append("o2o_employee e where ou.user_no = e.employee_no and
		// ou.is_remove = 0 ");

		StringBuffer querySqlBuf = new StringBuffer(
				"SELECT e.employee_no userNo,e.employee_namecn userName from o2o_employee e where e.employee_no in( ");
		querySqlBuf.append(
				" select DISTINCT * from (SELECT t.user_no from act_org_user t where t.is_remove = 0 USER_NO_CON ORG_ID_CON ");
		querySqlBuf.append(
				" union all select t.user_no from act_org_role_user t where t.is_remove = 0 USER_NO_CON ORG_ID_CON ) t ) ");
		String querySql = querySqlBuf.toString();

		if (StringUtils.isNotBlank(userNo)) {
			String userNoCon = " and t.user_no = '" + userNo + "'";
			querySql = querySql.replace("USER_NO_CON", userNoCon);
		} else {
			querySql = querySql.replace("USER_NO_CON", "");
		}

		if (StringUtils.isNotBlank(orgId)) {
			List<ActOrgEntity> orgList = sysOrgDao.getLeafNodeByParentId(orgId);
			if (orgList != null) {
				StringBuffer orgIdConBuf = new StringBuffer(" and t.org_id in (");
				for (ActOrgEntity org : orgList) {
					orgIdConBuf.append("'").append(org.getOrgId()).append("'").append(",");
				}
				String orgIdCon = orgIdConBuf.substring(0, orgIdConBuf.length() - 1) + ")";
				querySql = querySql.replace("ORG_ID_CON", orgIdCon);
			} else {
				querySql = querySql.replace("ORG_ID_CON", "");
			}
		} else {
			querySql = querySql.replace("ORG_ID_CON", "");
		}
		pageQuery.setQuerySql(querySql);
		return super.findDataWithPage(pageQuery);
	}

	@Override
	public PageResult<Object[]> getAllUser(EmployeeVo vo, int pageIndex) {
		String sql = "SELECT DISTINCT(u.user_no), e.employee_namecn as userName, " +
				" case  when e.is_dimission = 0 then '在职' when e.is_dimission = 1 then '离职' when e.is_dimission = 2 then '锁定'  else null end as is_dimission, case when e.join_date is null then '' else  e.join_date end " +
				" ,  e.province_name,  e.post_name  FROM act_org_user u INNER JOIN o2o_employee e ON u.user_no=e.employee_no  where 1=1 ";
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, 10, pageIndex);
		if(StringUtil.isNotBlank(vo.getUserNo())) {
			sqlPageQuery.addParam(vo.getUserNo());
			sql += " AND u.user_no=? ";
		}
		if(StringUtil.isNotBlank(vo.getUserName())) {
			sqlPageQuery.addLikeParam(vo.getUserName());
			sql += " AND e.employee_namecn like ? ";
		}
		if(null != vo.getIsDimission()) {
			sqlPageQuery.addParam(vo.getUserName());
			sql += " AND e.is_dimission=? ";
		}
		sql += " order by u.user_no ";
		sqlPageQuery.setQuerySql(sql);
		return super.findDataWithPage(sqlPageQuery);
	}

	@Override
	public Object[] queryUserEmployeeByUserNo(EmployeeVo vo) {
		String sql = " SELECT DISTINCT(u.user_no), e.employee_namecn as userName, " +
				" e.is_dimission, e.join_date,e.post_code,e.post_name,e.province_code,e.province_name  " +
				"  FROM act_org_user u INNER JOIN o2o_employee e ON u.user_no=e.employee_no WHERE u.user_no=? ";
		return super.findEntity(sql, Object[].class, new Object[] {vo.getUserNo()});
	}

	@Override
	public boolean editorUserStatus(EmployeeVo vo) {
		String provinceCode=null;
		String provinceName=null;
		if(StringUtil.isNotBlank(vo.getProvinceName())){
			provinceName = vo.getProvinceName().split("#")[0];
			provinceCode = vo.getProvinceName().split("#")[1];
		}
		String sql = " UPDATE o2o_employee e SET is_dimission=? , join_date=?,post_code=?,post_name=?,province_code=?,province_name=?  WHERE e.employee_no=? ";
		return super.execute(sql, new Object[] {vo.getIsDimission(), vo.getJoinDate(),vo.getPostCode(),vo.getPostName(),provinceCode,provinceName, vo.getUserNo()})>0;
	}

	@Override
	public void deleteUserResources(String userNo) {
		User user = UserHolder.getUser();
		String deleteOrgUserSql = "update act_org_user set is_remove = 1 , updator = ? , update_time = ? where user_no = ?";
		super.execute(deleteOrgUserSql, user.getNo(), new Date(), userNo);
		String deleteOrgRoleUserSql = "update act_org_role_user set is_remove = 1 , updator = ? , update_time = ? where user_no = ?";
		super.execute(deleteOrgRoleUserSql, user.getNo(), new Date(), userNo);
	}

	@Override
	public List<SysUser> getUserByOrgIdsAndRoleCode(List<String> orgIdList, String roleCode) {
		StringBuffer sqlBuffer = new StringBuffer(
				"select * from o2o_employee e where e.employee_no in (SELECT DISTINCT oru.user_no from act_org_role_user oru,o2o_act_role r "
						+ " where r.id = oru.role_id and r.role_code = ? and oru.is_remove = 0 ");
		if (!CollectionUtils.isEmpty(orgIdList)) {
			StringBuffer orgIdsBuffer = new StringBuffer(" and oru.org_id in (");
			for (String orgId : orgIdList) {
				orgIdsBuffer.append("'").append(orgId).append("'").append(",");
			}
			String orgIdsStr = orgIdsBuffer.substring(0, orgIdsBuffer.length() - 1) + ")";
			sqlBuffer.append(orgIdsStr);
		}
		sqlBuffer.append(" )");
		return workflowJdbcSession.findEntitys(sqlBuffer.toString(), SysUser.class, roleCode);
	}

	@Override
	public HREmployeeAuditor getEmployeeAuditor(String employNo, String auditorType) {
		String sql = "SELECT t.EMPLOYEENO employeeNo,t.EMPLOYEENAME employeeName,t.AUDITORTYPE auditorType,t.AUDITORNO auditorNo,t.AUDITORNAME auditorName from hr_employeeauditor t where t.EMPLOYEENO = ? and t.AUDITORTYPE = ? and t.is_del='N'";
		return workflowJdbcSession.findEntity(sql, HREmployeeAuditor.class, employNo, auditorType);
	}

	@Override
	public List<HREmployeeAuditor> getAuditorWithNo(String employNo) {
		String sql = "SELECT t.EMPLOYEENO employeeNo,t.EMPLOYEENAME employeeName,t.AUDITORTYPE auditorType,t.AUDITORNO auditorNo,t.AUDITORNAME auditorName from hr_employeeauditor t where t.EMPLOYEENO = ? and t.is_del='N'";
		return workflowJdbcSession.findEntitys(sql, HREmployeeAuditor.class, employNo);
	}

	@Override
	public User getUserInSystem(String userNo) {
		String sql ="SELECT t.user_no as no from act_org_user t where t.user_no = ? and t.is_remove =0";
		return workflowJdbcSession.findEntity(sql, User.class, userNo);
	}

	@Override
	public List<SysUser> getUserByOrgIdsAndRoleCode(List<String> orgIdList, List<String> roleCodeList) {

		StringBuffer sqlBuffer = new StringBuffer(
				"select * from o2o_employee e where e.employee_no in (SELECT DISTINCT oru.user_no from act_org_role_user oru,o2o_act_role r "
						+ " where r.id = oru.role_id and oru.is_remove = 0 ");
		if (!CollectionUtils.isEmpty(orgIdList)) {
			StringBuffer orgIdsBuffer = new StringBuffer(" and oru.org_id in (");
			for (String orgId : orgIdList) {
				orgIdsBuffer.append("'").append(orgId).append("'").append(",");
			}
			String orgIdsStr = orgIdsBuffer.substring(0, orgIdsBuffer.length() - 1) + ")";
			sqlBuffer.append(orgIdsStr);
		}

		if (!CollectionUtils.isEmpty(roleCodeList)) {
			StringBuffer roleIdsBuffer = new StringBuffer(" and r.role_code in (");
			for (String roleId : roleCodeList) {
				roleIdsBuffer.append("'").append(roleId).append("'").append(",");
			}
			String roleIdsStr = roleIdsBuffer.substring(0, roleIdsBuffer.length() - 1) + ")";
			sqlBuffer.append(roleIdsStr);
		}

		sqlBuffer.append(" )");

		return workflowJdbcSession.findEntitys(sqlBuffer.toString(), SysUser.class);

		// StringBuffer sqlBuf = new StringBuffer(
		// "select * from o2o_employee e where e.employee_no in (SELECT DISTINCT
		// oru.user_no from act_org_role_user oru,o2o_act_role r where r.id =
		// oru.role_id and oru.is_remove = 0 and r.role_code in (?) ");
		// if (!CollectionUtils.isEmpty(orgIdList)) {
		// sqlBuf.append(" and oru.org_id in (?))");
		// return workflowJdbcSession.findEntitys(sqlBuf.toString(),
		// SysUser.class, roleCodeList, orgIdList);
		// } else {
		// sqlBuf.append(")");
		// return workflowJdbcSession.findEntitys(sqlBuf.toString(),
		// SysUser.class, roleCodeList);
		// }
	}
	
	
	
	@Override
	public List<Object[]> getLikePostList(EmployeeVo vo) {
		String sql = "select t.code,t.name  from wf_dictionary_data t where t.type=16  and  t.name  LIKE ? LIMIT 10";
		return super.findObjectsList(sql, "%" + vo.getPostName() + "%");
	}
}
